﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports CodeMatrix.Context

Public Class StpDepartment

    Public Shared Function DeleteStpDepartment(ByVal tbl As String, ByVal pid As String, ByVal cid As String) As Int32

        Dim affectedRecord = -1
        Using c As New SqlConnection(CodeMatrix.Utilities.CodeHelper.ConnectionString)

            Dim cmd As New SqlCommand()
            cmd.CommandText = "EXEC  [dbo].[USP_GenericDelete] @tbl,@pid,@cid;"
            cmd.Connection = c

            cmd.Parameters.Add(New SqlParameter("@tbl", tbl))
            cmd.Parameters.Add(New SqlParameter("@pid", pid))
            cmd.Parameters.Add(New SqlParameter("@cid", cid))

            If c.State <> ConnectionState.Open Then
                c.Open()
            End If

            affectedRecord = cmd.ExecuteNonQuery()

            ' =========================================================
            ' Log command
            ' =========================================================
            Dim e As New OrderedDictionary()
            e.Add(pid, cid)
            GenericHelper.GenerateScript(GenerateScriptLogType.Delete, LogMenuType.stp_department, e)
        End Using

        Return affectedRecord

    End Function

    Public Shared Function GetDataTable(ByVal departmentName As String, ByVal masterDepartmentCode As Int32, ByVal status As Int32, ByVal staff_code As Int32) As DataTable

        ' =========================================================================
        ' Get table
        ' =========================================================================
        Dim tbl = CodeMatrix.Utilities.CodeHelper.GetDataTable( _
            "exec USP_Filter2_stp_auth_department @departmentName , @master_department_code ,@department_status, @staff_code", _
             New SqlParameter("@departmentName", departmentName), _
             New SqlParameter("@master_department_code", masterDepartmentCode), _
             New SqlParameter("@department_status", status), _
             New SqlParameter("@staff_code", staff_code) _
            )

        ' Modified by Boonyarit Yingvoragan
        ' Modified date 19/10/2553
        tbl = ArrangeData(tbl)

        Return tbl

    End Function

    'Public Shared Function GetDataTable(ByVal departmentName As String, ByVal masterDepartmentCode As Int32, ByVal status As Int32) As DataTable

    '    ' =========================================================================
    '    ' Get table
    '    ' =========================================================================
    '    Dim tbl = CodeMatrix.Utilities.CodeHelper.GetDataTable( _
    '        "exec USP_Filter2_stp_auth_department @departmentName , @master_department_code ,@department_status", _
    '         New SqlParameter("@departmentName", departmentName), _
    '         New SqlParameter("@master_department_code", masterDepartmentCode), _
    '         New SqlParameter("@department_status", status))

    '    ' Modified by Boonyarit Yingvoragan
    '    ' Modified date 19/10/2553
    '    tbl = ArrangeData(tbl, "&nbsp;&nbsp;")

    '    Return tbl

    'End Function

    Public Shared Function GetDataTable1(ByVal department_code As String, ByVal master_department_code As Int32) As DataTable

        'ByVal department_status As Int32

        ' =========================================================================
        ' Get table
        ' =========================================================================

        ' ,@department_status
        'New SqlParameter("@department_status", department_status) _

        Dim tbl = CodeMatrix.Utilities.CodeHelper.GetDataTable( _
            "exec USP_List_stp_department @department_code , @master_department_code", _
             New SqlParameter("@department_code", department_code), _
             New SqlParameter("@master_department_code", master_department_code))

        ' Modified by Boonyarit Yingvoragan
        ' Modified date 19/10/2553
        tbl = ArrangeData(tbl)

        Return tbl

    End Function

    Public Shared Function GetStpDepartment(ByVal masterDepartmentCode As Int32) As DataTable

        ' =========================================================================
        ' Get table
        ' =========================================================================
        Dim tbl = CodeMatrix.Utilities.CodeHelper.GetDataTable( _
            "exec USP_Filter2_stp_auth_department1 @master_department_code", _
             New SqlParameter("@master_department_code", masterDepartmentCode))

        ' Modified by Boonyarit Yingvoragan
        ' Modified date 19/10/2553
        tbl = ArrangeData(tbl)

        Return tbl

    End Function

    Public Shared Function GetDepartmentForStaff(ByVal department_code As String, ByVal staff_status As Int32, ByVal ctx_staff_code As Int32, ByVal ctx_department_code As Int32) As DataTable

        ' =========================================================================
        ' Get table
        ' =========================================================================
        Dim tbl = CodeMatrix.Utilities.CodeHelper.GetDataTable( _
            "exec USP_List_staff_report1 @department_code ,@staff_status ,@ctx_staff_code ,@ctx_department_code", _
             New SqlParameter("@department_code", department_code), _
             New SqlParameter("@staff_status", staff_status), _
             New SqlParameter("@ctx_staff_code", ctx_staff_code), _
             New SqlParameter("@ctx_department_code", ctx_department_code) _
            )

        ' Modified by Boonyarit Yingvoragan
        ' Modified date 19/10/2553
        tbl = GetDepartment(tbl)

        Return tbl

    End Function

    Public Shared Function GetDepartment(ByVal srcTbl As DataTable) As DataTable

        Dim result As String = ""

        For i As Integer = 0 To srcTbl.Rows.Count - 1

            result = GetParents(srcTbl.Rows(i)("staff_code"))

            srcTbl.Rows(i)("department_parent_name") = result

            'srcTbl.Rows(i)("department_hirachy") = result & "," & srcTbl.Rows(i)("department_hirachy")

        Next

        Return srcTbl

    End Function


    Private Shared Function GetParents(ByVal staffCode As Integer) As String

        Dim output As String = ""
        Dim dep_parent As Integer = 0

        ' Get department id
        Dim reTbl = CodeMatrix.Utilities.CodeHelper.GetDataTable( _
                   "select department_code from staff where staff_code = " & staffCode)

        Dim departmentcode As Integer = reTbl.Rows(0)(0)

        ' Call first time
        Dim reTblParent = CodeMatrix.Utilities.CodeHelper.GetDataTable( _
                    "select department_parent from stp_department where department_code = " & departmentcode)

        Do While Not (reTblParent.Rows(0)(0) Is DBNull.Value)

            Dim reTblDepartName = CodeMatrix.Utilities.CodeHelper.GetDataTable( _
                    "select department_name from stp_department where department_code = " & reTblParent.Rows(0)(0))

            If (Not String.IsNullOrEmpty(output)) Then
                'output = output & ","
                output = "," & output
            End If


            'output = output & reTblParent.Rows(0)(0).ToString()
            output = reTblDepartName.Rows(0)(0).ToString() & output

            ' Get next one
            reTblParent = CodeMatrix.Utilities.CodeHelper.GetDataTable( _
                    "select department_parent from stp_department where department_code = " & reTblParent.Rows(0)(0).ToString())
        Loop

        Return output

    End Function

    Public Shared Function GetEmptyColumn(ByVal srcTbl As DataTable) As DataTable
        Return srcTbl
    End Function

    Public Shared Function ArrangeData(ByVal srcTbl As DataTable) As DataTable

        Dim desTbl As New DataTable()
        Dim level As Int32 = 0

        Try

            desTbl = srcTbl.Clone()

            If srcTbl.Rows.Count > 0 Then
                ' Get the first level from first row
                Dim rootLevel As Int32 = srcTbl.Rows(0)("master_department_level")
                ' Get the DataRow from root level
                Dim rootLevelRow As DataRow() = srcTbl.Select("master_department_level=" & rootLevel)
                ' Build Tree from root level
                BuildTree(srcTbl, desTbl, rootLevelRow)
            End If

            Return desTbl

        Catch ex As Exception
            Throw ex
        End Try

    End Function

    Private Shared Sub BuildTree(ByVal srcTable As DataTable, ByVal desTable As DataTable, ByVal row() As DataRow)
        For i As Int32 = 0 To row.Length - 1
            desTable.ImportRow(row(i))
            Dim node As DataRow() = srcTable.Select("department_parent=" & row(i)("department_code"))
            BuildTree(srcTable, desTable, node)
        Next
    End Sub

    Private Shared Sub ArrangeDataNested(ByVal srcTbl As DataTable, ByVal desTbl As DataTable, ByVal pid As Int32, ByRef level As Int32, ByVal prefix As String)

        For Each i As DataRow In srcTbl.Select("department_parent = " & pid, "master_department_level")

            Dim iPid As Int32 = Convert.ToInt32(i("department_code"))

            ' ==========================================
            ' Copy row to destination table
            ' ==========================================
            i("department_name") = GetPrefix(i("department_name"), level, prefix)
            desTbl.Rows.Add(i.ItemArray)

            ' ==========================================
            ' Remove source table
            ' ==========================================
            srcTbl.Rows.RemoveAt(srcTbl.Rows.IndexOf(i))

            ' ==========================================
            ' Recursive whole data
            ' ==========================================
            level = level + 1
            ArrangeDataNested(srcTbl, desTbl, iPid, level, prefix)
            level = level - 1
        Next

    End Sub

    Private Shared Function GetPrefix(ByVal displayText As String, ByVal level As Int32, ByVal prefix As String) As String
        For i As Integer = 1 To level
            prefix &= prefix
        Next
        Return prefix & displayText
    End Function
End Class
